﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using br.com.SistemaChamados.QueryManager.Core;
using br.com.SistemaChamados.QueryManager.Utils;

namespace QueryManager.Core
{
    public class QueryManagerStatementConstructor
    {
        private static string[] TYPES_WITH_QUOTE = { "System.String", "System.DateTime" };

        public static string buildASelectSql(Type _typeClass)
        {
            string tableName = QueryManagerConstants.PREFIX_TABLE + _typeClass.Name;
            string aSql = QueryManagerConstants.TEMPLATE_SQL_SELECT;
            aSql = aSql.Replace(QueryManagerConstants.EXPRESSION_TABLE_NAME, tableName);
            return aSql;
        }


        public static string buildASelectSql(Type _typeClass, string fieldIdentifier, object value)
        {
            string tableName = QueryManagerConstants.PREFIX_TABLE + _typeClass.Name;
            string aSql = QueryManagerConstants.TEMPLATE_SQL_SELECT_IDENTIFIER;
            aSql = aSql.Replace(QueryManagerConstants.EXPRESSION_TABLE_NAME, tableName);
            aSql = aSql.Replace(QueryManagerConstants.EXPRESSION_IDENTIFIER_FIELD, fieldIdentifier);
            aSql = aSql.Replace(QueryManagerConstants.EXPRESSION_VALUE_FIELD, value.ToString());
            return aSql;
        }

        public static string buildAnInsertSql(object insertableObject, string[] propertiesInsertException)
        {
            string aSql = QueryManagerConstants.TEMPLATE_SQL_INSERT;
            string tableName = QueryManagerConstants.PREFIX_TABLE + insertableObject.GetType().Name;
            string[] insertArgs = buildFieldsAndValuesStatements(insertableObject, propertiesInsertException);
            aSql = aSql.Replace(QueryManagerConstants.EXPRESSION_TABLE_NAME, tableName);
            aSql = aSql.Replace(QueryManagerConstants.EXPRESSION_FIELDS_INSERT, insertArgs[0]); 
            aSql = aSql.Replace(QueryManagerConstants.EXPRESSION_VALUES_INSERT, insertArgs[1]); 
            return aSql;
        }


        private static string[] buildFieldsAndValuesStatements(object insertableObject, string[] propertiesInsertException)
        {
            string[] insertArgs = new string[2];
            insertArgs[0] = buildSqlStringWithSeparatorFields(insertableObject.GetType(), " , ", propertiesInsertException);
            insertArgs[1] = buildSqlStringWithSeparatorValues(insertableObject, " , ", propertiesInsertException);
            return insertArgs;
        }

        private static string buildSqlStringWithSeparatorFields(Type typeObject, string separator, string[] propertiesInsertException)
        {
            int propertiesLenght = typeObject.GetProperties().Length;
            string[] propertyNames = ReflectionUtils.getPropertyNamesFromObject(typeObject);
            string fieldsWithSeparator = "";
            for (int i = 0; i < propertiesLenght; i++)
            {
                if (!propertiesInsertException.Contains(propertyNames[i]))
                    fieldsWithSeparator += QueryManagerConstants.PREFIX_FIELD + typeObject.Name + propertyNames[i] +
                              separator + " ";
            }
            fieldsWithSeparator = fieldsWithSeparator.Substring(0, fieldsWithSeparator.LastIndexOf(separator));
            return fieldsWithSeparator;
        }

        private static string buildSqlStringWithSeparatorValues(object aObject, string separator, string[] propertiesInsertException)
        {
            string stringFieldsValuesWithSeparator = "";
            Type typeObject = aObject.GetType();
            int propertiesLenght = aObject.GetType().GetProperties().Length;
            string[] propertyNames = ReflectionUtils.getPropertyNamesFromObject(typeObject);
            for (int i = 0; i < propertiesLenght; i++)
            {
                if (!propertiesInsertException.Contains(propertyNames[i]))
                {
                    if (TYPES_WITH_QUOTE.Contains(typeObject.GetProperties()[i].GetValue(aObject, null).GetType().FullName))
                    {
                        stringFieldsValuesWithSeparator += "'" + typeObject.GetProperties()[i].GetValue(aObject, null) + "'" +
                                  separator + " ";
                    }
                    else
                    {
                        stringFieldsValuesWithSeparator += typeObject.GetProperties()[i].GetValue(aObject, null) +
                                  separator + " ";
                    }
                }
            }
            stringFieldsValuesWithSeparator = stringFieldsValuesWithSeparator.Substring(0, stringFieldsValuesWithSeparator.LastIndexOf(separator));
            return stringFieldsValuesWithSeparator;
        }

        public static string buildAUpdateSql(object updatableObject, string identifierField, object valueField)
        {
            string aSql = QueryManagerConstants.TEMPLATE_SQL_UPDATE;
            string tableName = QueryManagerConstants.PREFIX_TABLE + updatableObject.GetType().Name;
            string setFields = buildSetFieldsToUpdate(updatableObject);
            string updateCondition = "";
            string convertedIdentifierField = QueryManagerConstants.PREFIX_FIELD + updatableObject.GetType().Name + identifierField;
            if (TYPES_WITH_QUOTE.Contains(valueField.GetType().FullName))
            {
                updateCondition = convertedIdentifierField + "' = '" + valueField.ToString() + "'";
            }
            else
            {
                updateCondition = convertedIdentifierField + " = " + valueField.ToString();
            }
            aSql = aSql.Replace(QueryManagerConstants.EXPRESSION_TABLE_NAME, tableName);
            aSql = aSql.Replace(QueryManagerConstants.EXPRESSION_SET_FIELDS, setFields);
            aSql = aSql.Replace(QueryManagerConstants.EXPRESSION_CONDITIONS_UPDATE_DELETE, updateCondition);
            return aSql;
        }

        private static string buildSetFieldsToUpdate(object updatableObject)
        {
            string filter = "";
            filter = buildSqlStringWithSeparatorFieldsAndValues(updatableObject, " , ");
            return filter;
        }

        private static string buildSqlStringWithSeparatorFieldsAndValues(object aObject, string separator)
        {
            string stringFieldsValuesWithSeparator = "";
            Type typeObject = aObject.GetType();
            int propertiesLenght = aObject.GetType().GetProperties().Length;
            string[] propertyNames = ReflectionUtils.getPropertyNamesFromObject(typeObject);
            for (int i = 0; i < propertiesLenght; i++)
            {
                if (typeObject.GetProperties()[i].GetValue(aObject, null) != null)
                {
                    stringFieldsValuesWithSeparator += QueryManagerConstants.PREFIX_FIELD + typeObject.Name + propertyNames[i] +
                              " = ";
                    if (TYPES_WITH_QUOTE.Contains(typeObject.GetProperties()[i].GetValue(aObject, null).GetType().FullName))
                    {
                        stringFieldsValuesWithSeparator += "'" + typeObject.GetProperties()[i].GetValue(aObject, null) + "'" +
                                  separator;
                    }
                    else
                    {
                        stringFieldsValuesWithSeparator += typeObject.GetProperties()[i].GetValue(aObject, null) +
                                  separator;
                    }
                }
            }
            stringFieldsValuesWithSeparator = stringFieldsValuesWithSeparator.Substring(0, stringFieldsValuesWithSeparator.LastIndexOf(separator));
            return stringFieldsValuesWithSeparator;
        }

        public static string buildADeleteSql(object deletableObject)
        {
            string tableName = QueryManagerConstants.PREFIX_TABLE + deletableObject.GetType().Name;
            string queryFilter = buildFilterToDelete(deletableObject);
            string aSql = QueryManagerConstants.TEMPLATE_SQL_DELETE;
            aSql = aSql.Replace(QueryManagerConstants.EXPRESSION_TABLE_NAME, tableName);
            if (queryFilter.IndexOf("AND") > 0)
            {
                aSql = aSql.Replace(QueryManagerConstants.EXPRESSION_CONDITIONS_UPDATE_DELETE, queryFilter.Substring(0, queryFilter.IndexOf("AND")));
            }
            else
            {
                aSql = aSql.Replace(QueryManagerConstants.EXPRESSION_CONDITIONS_UPDATE_DELETE, queryFilter);
            }
            return aSql;
        }

        private static string buildFilterToDelete(object deletableObject)
        {
            string filter = "";
            filter = buildSqlStringWithSeparatorFieldsAndValues(deletableObject, " AND ");
            return filter;
        }

    }
}
